package com.psa.tool;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.psa.element.ISqlObject;
import com.psa.system.MainActivity;
import com.wxw.BaseLibrary.tool.L;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author david
 * @date 14-3-12
 */
public class DBTool extends SQLiteOpenHelper{

    private static DBTool dbTool;
    public static Context mContext = null;
    private static int mVersion = SystemStatus.DatabaseVersion;

    private DBTool(Context context, int version) {
        super(context, SystemStatus.DATABASE_NAME, null, version);
    }

    public synchronized static DBTool getInstance(Context context){
        if (dbTool == null) {
            dbTool = new DBTool(context, mVersion);
        }
        return dbTool;
    }

    public synchronized static DBTool getInstance(){
        if (mContext == null || mVersion < 0) {
            return null;
        }
        return getInstance(mContext);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS Customer(ID nvarchar(15),ChineseName nvarchar(100),ChineseAddress nvarchar(250),EnglishName nvarchar(150),"
                + "EnglishAddress nvarchar(300),Contact nvarchar(20),Telephone nvarchar(30),BusinessType nvarchar(6),TradeMode nvarchar(6),"
                + "DistrictID nvarchar(3),CreditLimit int,isActive int,PrintNoOfDeliveryNote int,PrintNoOfInvoice int,BillCompany nvarchar(2),"
                + "BillLanguage nvarchar(2),ManagedDepartment nvarchar(5),Remark nvarchar(300),WeeklyRoutine int,SequenceNo int)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS OperatorCustomer(Weekday int,DistrictID nvarchar(3),SeqNo int,CustomerID nvarchar(15))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS TradeMode(ID nvarchar(5),ChineseName nvarchar(60),EnglishName nvarchar(60))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS District(ID nvarchar(5),ChineseName nvarchar(60),EnglishName nvarchar(60), Routine nvarchar(1))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS Department(ID nvarchar(5),ChineseName nvarchar(60),EnglishName nvarchar(60))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS Channel(ID nvarchar(5),ChineseName nvarchar(60),EnglishName nvarchar(60))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS Package(ID nvarchar(5),ChineseName nvarchar(60),EnglishName nvarchar(60))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS Brand(ID nvarchar(5),ChineseName nvarchar(60),EnglishName nvarchar(60))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS Merchandise(ID nvarchar(12),NameChinese nvarchar(60),PackingNameChinese1 nvarchar(2),PackingNameChinese2 nvarchar(2),Price float,Packing int,Brand nvarchar(5),Package nvarchar(5))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS CustomerVisit(ID integer Primary key AUTOINCREMENT, CustomerID nvarchar(15), VisitType int, VisitDatetime nvarchar(20), CompleteDatetime nvarchar(20), Remark nvarchar(300), VType int)");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS MerchandiseSeq(MerchandiseID nvarchar(12),SequenceNo int)");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS OrderBill(OrderId nvarchar(64) PRIMARY KEY,CustomerID nvarchar(15),OrderTime nvarchar(20),OrderType int,Address1 nvarchar(60),"
            + "Address2 nvarchar(60),Remark nvarchar(100),ShipDate int,ShipStartTime nvarchar(20),ShipEndTime nvarchar(20),Distributor nvarchar(15),isCredit int,PPICode int, InvoiceCode int, NCCode nvarchar(20), Status int, StatusMsg nvarchar(300))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS OrderItem(_id INTEGER PRIMARY KEY AUTOINCREMENT,MerchandiseID nvarchar(12),"
                + "OrderId nvarchar(64),QTYText nvarchar(10),SalesType nvarchar(10),QTY int,Price float,Sum float,PromoCode nvarchar(20),Status int, StatusMsg nvarchar(300))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS StockItem(_id INTEGER PRIMARY KEY AUTOINCREMENT,MerchandiseID nvarchar(12),"
            + "QTY int,StockCode int, ProdDate nvarchar(20))");
        sqLiteDatabase.execSQL("Create Table IF NOT EXISTS StockBill(_id INTEGER PRIMARY KEY AUTOINCREMENT,CustomerID nvarchar(15),StockDate nvarchar(20))");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS OrderInvoice(BillNo int,BillDate nvarchar(20),CustomerID nvarchar(15),Amount float,isCredit int,OrderDate nvarchar(20),StatisticsCode1 nvarchar(10),StatisticsTime1 nvarchar(20))");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS OrderInvoiceItem(BillNo int,MerchandiseID nvarchar(12),SalesType nvarchar(10),Quantity int,Price float)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS Interchange(ID int Primary Key,Target TEXT,Requirement TEXT,Status TEXT,Contents TEXT,Message TEXT)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS CustomerPicture(PictureName nvarchar(50),CustomerID nvarchar(15),PictureDemo nvarchar(200),PictureDate nvarchar(20),PictureStatus nvarchar(5),PictureType int)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS Distributor(CustomerID nvarchar(15), DistributorName nvarchar(50), pk_org nvarchar(20), changeprice int, def int, distributortype int)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS MerchandisePrice(ProdCode nvarchar(10), CustCode nvarchar(15), PriceType nvarchar(5), PriceLevel int, Price float)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS QuerySetting(ID int, name nvarchar(30), key nvarchar(20), setting text, scope int)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS pda_defdoc(pk_id nvarchar(30), name nvarchar(200), dr int)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS pda_defdoclist(pk_id nvarchar(30), pk_defdoc nvarchar(30), name nvarchar(200), content nvarchar(500), datatype int, dr int)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS Showcase(pk_id int, name nvarchar(200), detail nvarchar(500), startdate nvarchar(20), enddate nvarchar(20), orderseq int, dr int)");
        execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS CustomerShowcase(pk_id INTEGER PRIMARY KEY AUTOINCREMENT, customerid nvarchar(15), showcaseid int, qydate nvarchar(20), jsdate nvarchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        //database version 30
        if (newVersion==31) {
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS Distributor(CustomerID nvarchar(15), DistributorName nvarchar(50), pk_org nvarchar(20), changeprice int, def int, distributortype int)");
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS MerchandisePrice(ProdCode nvarchar(10), CustCode nvarchar(15), PriceType nvarchar(5), PriceLevel int, Price float)");
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS QuerySetting(ID int, name nvarchar(30), key nvarchar(20), setting text, scope int)");
        }
        if (newVersion == 32) {
            execSQL(sqLiteDatabase, "Alter Table CustomerPicture Add PictureType int");
        }

        if (newVersion == 34) {
            execSQL(sqLiteDatabase, "Alter Table CustomerVisit Add VType int");
            execSQL(sqLiteDatabase, "Alter Table StockItem Add ProdDate nvarchar(20)");
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS pda_defdoc(pk_id nvarchar(30), name nvarchar(200), dr int)");
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS pda_defdoclist(pk_id nvarchar(30), pk_defdoc nvarchar(30), name nvarchar(200), content nvarchar(500), datatype int, dr int)");
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS Showcase(pk_id int, name nvarchar(200), detail nvarchar(500), startdate nvarchar(20), enddate nvarchar(20), orderseq int, dr int)");
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS CustomerShowcase(pk_id INTEGER PRIMARY KEY AUTOINCREMENT, customerid nvarchar(15), showcaseid int, qydate nvarchar(20), jsdate nvarchar(20))");
        }

        if (newVersion == 35) {
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS tt(OrderId nvarchar(64) PRIMARY KEY," +
                    "CustomerID nvarchar(15),OrderTime nvarchar(20),OrderType int,Address1 nvarchar(60)," +
                    "Address2 nvarchar(60),Remark nvarchar(100),ShipDate int,ShipStartTime nvarchar(20)," +
                    "ShipEndTime nvarchar(20),Distributor nvarchar(15),isCredit int,PPICode int," +
                    "InvoiceCode int, NCCode nvarchar(20), Status int, StatusMsg nvarchar(300))");
            execSQL(sqLiteDatabase, "Create Table IF NOT EXISTS tti(_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "MerchandiseID nvarchar(12),OrderId nvarchar(64),QTYText nvarchar(10),SalesType nvarchar(10)," +
                    "QTY int,Price float,Sum float,PromoCode nvarchar(20),Status int, StatusMsg nvarchar(300))");
            execSQL(sqLiteDatabase, "insert into tt select cast(_id as nvarchar) as OrderId,CustomerID,OrderTime," +
                    "OrderType,Address1,Address2,Remark,ShipDate,ShipStartTime,ShipEndTime,Distributor,isCredit,PPICode," +
                    "InvoiceCode,NCCode,Status,StatusMsg from OrderBill");
            execSQL(sqLiteDatabase, "insert into tti(MerchandiseID,OrderId,QTYText,SalesType," +
                    "QTY,Price,[Sum],PromoCode,Status,StatusMsg) select MerchandiseID," +
                    "cast(OrderCode as nvarchar) as OrderId,QTYText,SalesType," +
                    "QTY,Price,[Sum],PromoCode,Status,StatusMsg from OrderItem");
            execSQL(sqLiteDatabase, "drop table OrderBill");
            execSQL(sqLiteDatabase, "alter table tt rename to OrderBill");
            execSQL(sqLiteDatabase, "drop table OrderItem");
            execSQL(sqLiteDatabase, "alter table tti rename to OrderItem");
        }
    }

    public synchronized void execSQL(SQLiteDatabase db, String sql){
        try {
            db.execSQL(sql);
        } catch (Exception e) {
            Log.e(SystemStatus.TAG, e.getMessage());
            L.WriteExceptionLog("DBTool", "execSQL(?,?)", e.getMessage());
            ((MainActivity)SystemStatus.MainActivity).showMessage("DBTool.execSQL Exception:"+e.getMessage());
        }
    }

    public synchronized void execSQL(String sql) {
        try {
            SQLiteDatabase db = getReadableDatabase();
            db.execSQL(sql);
        } catch (Exception e) {
            Log.e(SystemStatus.TAG, e.getMessage());
            L.WriteExceptionLog("DBTool", "execSQL(?)", e.getMessage());
            ((MainActivity)SystemStatus.MainActivity).showMessage("DBTool.execSQL Exception:"+e.getMessage());
        }
    }

    public synchronized Cursor query(String sql) {
        Cursor cursor = null;
        try {
            SQLiteDatabase db = getReadableDatabase();
            cursor = db.rawQuery(sql, null);
        } catch (Exception e) {
            Log.e(SystemStatus.TAG,"DBTool.query:" + e.getMessage());
            L.WriteExceptionLog("DBTool", "query", e.getMessage());
            ((MainActivity)SystemStatus.MainActivity).showMessage("DBTool.query Exception:"+e.getMessage());
        }
        return cursor;
    }

    public synchronized long insert(String tableName, ContentValues values) {
        long i = 0;
        try {
            SQLiteDatabase db = getReadableDatabase();
            i = db.insert(tableName, null, values);
        } catch (Exception e) {
            Log.e(SystemStatus.TAG,"DBTool.insert:" + e.getMessage());
            L.WriteExceptionLog("DBTool", "insert", e.getMessage());
            ((MainActivity)SystemStatus.MainActivity).showMessage("DBTool.insert Exception:"+e.getMessage());
        }
        return i;
    }

    public synchronized int update(String tableName, ContentValues values, String where, String[] whereval) {
        int i = 0;
        try {
            SQLiteDatabase db = getReadableDatabase();
            i = db.update(tableName, values, where, whereval);
        } catch (Exception e) {
            Log.e(SystemStatus.TAG,"DBTool.update:" + e.getMessage());
            L.WriteExceptionLog("DBTool", "update", e.getMessage());
            ((MainActivity)SystemStatus.MainActivity).showMessage("DBTool.update Exception:"+e.getMessage());
        }
        return i;
    }

    public synchronized int delete(String tableName, String where, String[] whereval) {
        int i = 0;
        try {
            SQLiteDatabase db = getReadableDatabase();
            i = db.delete(tableName, where, whereval);
        } catch (Exception e) {
            Log.e(SystemStatus.TAG,"DBTool.delete:" + e.getMessage());
            L.WriteExceptionLog("DBTool", "delete", e.getMessage());
            ((MainActivity)SystemStatus.MainActivity).showMessage("DBTool.delete Exception:"+e.getMessage());
        }
        return i;
    }

    public static <T extends ISqlObject> List<T> convertCursorToList(Cursor cursor, Class<T> c){
        List<T> tList = new ArrayList<T>();
        try {
            T t = c.newInstance();
            while ((t = (T)t.full(cursor)) != null) {
                tList.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (cursor != null) cursor.close();
        return tList;
    }
}
